package jnpf.database.sql.util;

import jnpf.database.enums.DbAliasEnum;
import jnpf.database.enums.ParamEnum;
import jnpf.database.model.dbfield.DbFieldModel;
import jnpf.database.model.dbtable.DbTableFieldModel;
import jnpf.database.model.dto.PrepareSqlDTO;
import jnpf.database.model.interfaces.DataSourceMod;
import jnpf.database.source.DbBase;
import jnpf.database.sql.enums.SqlOracleEnum;
import jnpf.database.sql.enums.base.SqlComEnum;
import jnpf.database.sql.enums.base.SqlFrameBase;
import jnpf.database.util.DbTypeUtil;
import jnpf.database.util.JdbcUtil;
import jnpf.exception.DataException;
import jnpf.util.StringUtil;
import lombok.Cleanup;
import org.apache.commons.collections4.CollectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 常用SQL快捷使用工具
 *
 * @author JNPF开发平台组 YanYu
 * @version V3.3
 * @copyright 引迈信息技术有限公司
 * @date 2022-06-11
 */
public class SqlFastUtil {

    /**
     * 系统表参数获取
     */
    public static PrepareSqlDTO getSysPreDTO(Connection conn, SqlComEnum sqlComEnum, DataSourceMod dataSourceMod, String table) throws DataException {
        SqlFrameBase sysTemSqlEnum = sqlComEnum.getSqlFrameEnum(DbTypeUtil.getDb(conn).getJnpfDbEncode());
        return new PrepareSqlDTO(conn, sysTemSqlEnum.getSqlFrame(), sysTemSqlEnum.getStructParams(table, dataSourceMod));
    }

    /**
     * 添加表
     */
    public static void creTable(Connection conn, DbTableFieldModel dbTableFieldModel) throws Exception {
        List<DbFieldModel> dbFieldModelList = dbTableFieldModel.getDbFieldModelList();
        DbBase dbBase = DbTypeUtil.getDb(conn);
        // 生成表
        boolean autoInc = false;
        String oracleAutoIncField = "";
        if(CollectionUtils.isNotEmpty(dbFieldModelList)){
            String fieldListSql = "";
            for(DbFieldModel fieldModel : dbFieldModelList){
                String fieldSql = SqlComEnum.CREATE_TABLE.getOutSql(
                    dbBase,
                    // 表
                    "",
                    // 字段
                    dbBase.getJnpfDbEncode().equals(DbBase.MYSQL) ? "`" + fieldModel.getField() + "`" : fieldModel.getField(),
                    // 数据类型
                    fieldModel.formatDataTypeByView(dbBase.getJnpfDbEncode()),
                    // 非空
                    " " + fieldModel.getNullSign() + " ",
                    // 默认值
                    "",
                    // 主键
                    fieldModel.getIsPrimaryKey() != null && fieldModel.getIsPrimaryKey()  ? " PRIMARY KEY " : "",
                    // 自增
                    fieldModel.getIsAutoIncrement() != null && fieldModel.getIsAutoIncrement() ? " AUTO_INCREMENT " : "",
                    // 注释
                    "COMMENT '" + fieldModel.getComment() + "'"
                );
                String onwFieldSql = "";
                Matcher matcher = Pattern.compile("【(.+)】").matcher(fieldSql);
                if(matcher.find()){
                    onwFieldSql =  matcher.group(1);
                }else {
                    throw new DataException("未找到字段SQL语句");
                }
                fieldListSql = fieldListSql + "\t" + onwFieldSql + ",\n";
                if(fieldModel.getIsAutoIncrement() != null && fieldModel.getIsAutoIncrement()){
                    oracleAutoIncField = fieldModel.getField();
                    autoInc = true;
                }
            }
            // 去掉最后一个逗号
            fieldListSql = fieldListSql.replaceAll(",$", "");
            String sql = SqlComEnum.CREATE_TABLE.getOutSql(SqlFrameUtil.htmlE(dbTableFieldModel.getTable())).replaceAll("【.+】", fieldListSql);
            JdbcUtil.creUpDe(new PrepareSqlDTO(conn, sql));
            // ORACLE特殊自增方式
            if(dbBase.getJnpfDbEncode().equals(DbBase.ORACLE)){
                if(autoInc){
                    JdbcUtil.creUpDe(new PrepareSqlDTO(conn, SqlOracleEnum.CREATE_AUTO_INCREMENT.getSqlFrame().replace("{table}", dbTableFieldModel.getTable())));
                    String triggerSql = SqlOracleEnum.CREATE_AUTO_INCREMENT_TRIGGER.getSqlFrame()
                            .replace("{table}", dbTableFieldModel.getTable()).replace("{autoInc_field}", oracleAutoIncField);
                    @Cleanup PreparedStatement statement = conn.prepareStatement(sql);
                    statement.execute(triggerSql);
                }
            }
        }else {
            throw new Exception("没有初始字段");
        }
        // 生成注释
        commentTable(conn, dbTableFieldModel.getTable(), dbTableFieldModel.getComment());
        if(!dbBase.getJnpfDbEncode().equals(DbBase.MYSQL)){
            creTableCommentFiled(conn, dbTableFieldModel.getTable(), dbFieldModelList);
        }
    }

    public static void addField(Connection conn, String table, List<DbFieldModel> dbFieldModelList) throws Exception {
        DbBase dbBase = DbTypeUtil.getDb(conn);
        for (DbFieldModel dbFieldModel : dbFieldModelList) {
            String sql = SqlComEnum.ADD_COLUMN.getOutSql(
                    dbBase,
                    "ADD",
                    table,
                    dbFieldModel.getField(),
                    dbFieldModel.formatDataTypeByView(dbBase.getJnpfDbEncode()),
                    dbFieldModel.getNullSign(),
                    "",
                    "'" + dbFieldModel.getComment() + "'"
                    );
            JdbcUtil.creUpDe(new PrepareSqlDTO(conn, sql));
        }
        if(!DbTypeUtil.getDb(conn).getJnpfDbEncode().equals(DbBase.MYSQL)){
            creTableCommentFiled(conn, table, dbFieldModelList);
        }
    }

    /**
     * 删表
     */
    public static Boolean dropTable(Connection conn, String table) throws Exception {
        return JdbcUtil.creUpDe(new PrepareSqlDTO(conn, SqlComEnum.DROP_TABLE.getOutSql(DbTypeUtil.getDb(conn), SqlFrameUtil.htmlE(table))));
    }

    /**
     * 表重命名
     */
    public static Boolean reTableName(Connection conn, String oldTable, String newTable) throws Exception {
        return JdbcUtil.creUpDe(new PrepareSqlDTO(conn, SqlComEnum.RE_TABLE_NAME.getOutSql(DbTypeUtil.getDb(conn), oldTable, newTable)));
    }

    /**
     * 表注释
     */
    public static Boolean commentTable(Connection conn, String table, String comment) throws Exception {
        return JdbcUtil.creUpDe(new PrepareSqlDTO(conn, SqlComEnum.COMMENT_TABLE.getOutSql(DbTypeUtil.getDb(conn),
                SqlFrameUtil.htmlE(table),"'" + SqlFrameUtil.htmlE(comment) + "'")));
    }

    /**
     * 批量字段注释
     */
    private static List<Boolean> creTableCommentFiled(Connection conn, String table, List<DbFieldModel> dbFieldModelList) throws Exception {
        DbBase dbBase = DbTypeUtil.getDb(conn);
        List<PrepareSqlDTO> DTOs = new ArrayList<>();
        for (DbFieldModel dbFieldModel : dbFieldModelList) {
            String sql = SqlComEnum.COMMENT_COLUMN.getOutSql(DbTypeUtil.getDb(conn),
                    table,
                    dbFieldModel.getField(),
                    "'" + dbFieldModel.getComment() + "'",
                    dbFieldModel.formatDataTypeByView(dbBase.getJnpfDbEncode()),
                    null);
            DTOs.add(new PrepareSqlDTO(conn, sql));
        }
        return JdbcUtil.creUpDeBatch(conn, DTOs);
    }

    /**
     * 字段注释
     */
    public static Boolean commentFiled(Connection conn, String table, String column, String dataType, String comment) throws Exception {
        String sql = SqlComEnum.COMMENT_COLUMN.getOutSql(DbTypeUtil.getDb(conn), table, column, ParamEnum.COMMENT.getParamSign(), dataType, null);
        return JdbcUtil.creUpDe(new PrepareSqlDTO(conn, sql, comment));
    }

    /**
     * 获取分页SQL语句
     * @param selectSql 查询SQL语句
     * @param orderColumn 排序字段
     * @param orderSign
     *              ASC（ascend）：升序 1234 放空默认
     *              DESC（descend）：降序 4321
     * @param currentPage 当前页
     * @param pageSize 页面大小
     * @return String[] 0：分页查询语句 1：统计条数语句
     */
    public static String[] getPageSql(DbBase db, String selectSql, String orderColumn, String orderSign, Integer currentPage, Integer pageSize) throws DataException{
        if(StringUtil.isEmpty(orderColumn)){
            throw new DataException("分页查询缺少排序字段");
        }
        orderSign = StringUtil.isEmpty(orderSign) ? "" : orderSign;
        String beginIndex = Integer.toString((currentPage - 1) * pageSize);
        String endIndex = Integer.toString(currentPage * pageSize);
        return new String[]{
                SqlComEnum.ORDER_PAGE.getOutSql(db, selectSql, orderColumn, beginIndex, endIndex, pageSize.toString(), orderSign),
                SqlComEnum.COUNT_SIZE.getOutSql(db, DbAliasEnum.TOTAL_RECORD.asByDb(db), selectSql)
        };

    }
    /**
     * 批量添加
     */
    public static void batchInsert(Connection conn, String table, List<String> fieldList, List<List<?>> multiDataList) throws Exception {
        PrepareSqlDTO prepareSqlDTO = getInsertPrepareDTO(conn, table, fieldList);
        for (List<?> dataList : multiDataList) {
            prepareSqlDTO.addMultiData(dataList);
        }
        JdbcUtil.creUpDeBatchOneSql(prepareSqlDTO);
    }

    /**
     * 单条插入
     */
    public static void insert(Connection conn, String table, List<String> fieldList, List<?> dataList) throws Exception {
        PrepareSqlDTO prepareSqlDTO = getInsertPrepareDTO(conn, table, fieldList);
        prepareSqlDTO.setDataList(dataList);
        JdbcUtil.creUpDe(prepareSqlDTO);
    }

    private static PrepareSqlDTO getInsertPrepareDTO(Connection conn, String table, List<String> fieldList) throws Exception {
        DbBase db = DbTypeUtil.getDb(conn);
        List<String> signList = new ArrayList<>();
        for (int i = 0; i < fieldList.size(); i++) {
            signList.add("?");
        }
        String sql = SqlComEnum.INSERT.getOutSql(db, table, String.join(",", fieldList), String.join(",", signList));
        return new PrepareSqlDTO(conn, sql);
    }

    /**
     * 模糊查询
     */
    public static String getFuzzyQuerySql(String selectSql, String column, String keyWord) throws DataException {
        if (StringUtil.isNotEmpty(keyWord)) {
            return SqlComEnum.LIKE.getOutSql(selectSql, column, "%" + keyWord + "%");
        }
        return selectSql;
    }

    /**
     * 判断表数据存在
     */
    public static Boolean tableDataExist(Connection conn, String table) throws Exception {
        return JdbcUtil.queryOneInt(
                new PrepareSqlDTO(conn, SqlComEnum.COUNT_SIZE_TABLE.getOutSql(DbTypeUtil.getDb(conn),
                        "F_COUNT", table)),"F_COUNT") > 0;
    }

}
